* QCEW_collapse.do
* 2015.02.24
* Last update 2015.06.23: restored from archive
* Creates QCEW cross section

capture log close
set more off
timer clear 1
timer on 1
clear
set matsize 10000

local work "/DIRECTORY"

log using "`work'/logs/QCEW_collapse.log", replace


**************************
* QCEW
**************************
use "`work'/data/qcew/QCEW_master.dta", clear

* Subset to balanced panel of counties
bysort AreaCode: gen obs=_N
keep if obs==96
drop obs

* Variable handling
* Convention will be renaming QCEW variables to ATUS counterparts
rename median_age age
drop sunset_time_avg
rename SST1990 sunset_time_avg
gen obs=1

collapse (mean) pct* AverageWeeklyWage10 AverageWeeklyWage101 AverageWeeklyWage102 pop_density naics_* occ_* ///
	(median) median_age=age (sum) AvgEmployment10 obs, by(fips coast_dist latitude longitude tzcode statecode sunset_time_avg)

* Post-collapse variable handling
gen ln_avwage10 = log(AverageWeeklyWage10)
gen ln_avwage101 = log(AverageWeeklyWage101)
gen ln_avwage102 = log(AverageWeeklyWage102)
gen ln_pop_density = log(pop_density)
gen group = 1
encode fips, generate(fipsnum)

* Creating indicator for coastal counties
* Dropping 6 records for which _merge==2
merge m:1 fips using "`work'/data/coastal_counties.dta", keep(1 3)
gen coastal = (_merge == 3)
gen coast = (coastal==1 & coast_dist < 1.5)
gen cc = coast_dist*coast
drop _merge

* Saving
save "`work'/data/qcew/QCEW_xsec.dta", replace


timer off 1
timer list 1
capture log close



